import DBConnect
import datetime
from pymysql.cursors import DictCursor
import getRiskPoints
import json


def insert_to_db(data_list):
    db = DBConnect.connect()

    cursor = db.cursor()
    for data in data_list:
        # check if already exists
        sql = "SELECT COUNT(*) FROM riskpoints WHERE location = \'{}\'".format(data['riskPoint'])
        cursor.execute(sql)
        s = cursor.fetchall()[0][0]
        if s == 0:
            location, startTime, endTime, lng, lat = data['riskPoint'], data['startTime'], data['endTime'], data['lng'], data['lat']
            # YYYY-MM-DD HH:MM:SS
            startTime = "{0}-{1}-{2} {3}".format(*startTime.split('-'))
            endTime = "{0}-{1}-{2} {3}".format(*endTime.split('-'))
            sql = ("INSERT INTO riskpoints(location, startTime, endTime, lng, lat) VALUES (\"{}\", \"{}\", \"{}\", \"{}\", \"{}\")". \
                format(location, startTime, endTime, lng, lat))
            try:
                cursor.execute(sql)
                db.commit()
            except:
                db.rollback()
    
    db.close()


def get_riskPoints_by_days(days: int):
    db = DBConnect.connect()

    now = datetime.datetime.now()

    cursor = db.cursor(DictCursor)
    sql = ("SELECT * FROM riskpoints WHERE DATEDIFF(CURDATE(), DATE(endTime)) <= {}".format(days))
    cursor.execute(sql)
    data = cursor.fetchall()
    for d in data:
        d['startTime'] = d['startTime'].strftime("%Y-%m-%d %H:%M:%S")
        d['endTime'] = d['endTime'].strftime("%Y-%m-%d %H:%M:%S")
    data = json.dumps(data, ensure_ascii=False)
    db.close()
    return data


def update_db():
    getRiskPoints.get_data()
    data_list = json.load(open("../data/riskpoints.json", encoding="utf-8"))
    insert_to_db(data_list)


if __name__ == "__main__":

    update_db()
    pass